package vn.edu.nuce.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;

import vn.edu.nuce.model.LichDangKy;
import vn.edu.nuce.model.LichDangKyStatus;
import vn.edu.nuce.model.ThongKe;

public class LichDangKyDaoImpl implements LichDangKyDao{
	
	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	public void addLichDangKy(LichDangKy lichDangKy) {
		getSessionFactory().getCurrentSession().save(lichDangKy);		
	}

	public void updateLichDangKy(LichDangKy lichDangKy) {
		getSessionFactory().getCurrentSession().update(lichDangKy);
	}

	public void deleteLichDangKy(String id) {
		getSessionFactory().getCurrentSession().delete(getLichDangKyById(id));
	}

	public LichDangKy getLichDangKyById(String id) {
		return (LichDangKy) getSessionFactory().getCurrentSession().get(LichDangKy.class, id);
	}

	public List<LichDangKy> getLichDangKy() {
		// TODO Auto-generated method stub
		return null;
	}

	public List<LichDangKy> getLichDangKyByDate(Date date) {
		String queryString = "FROM LichDangKy WHERE ngay_dat = ?";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, date);
		return hql.list();
	}

	public List<LichDangKy> getDsCho(String idPhongMay, String idTietHoc,
			Date date) {
		String queryString = "FROM LichDangKy WHERE id_phong = ? AND id_tiet_hoc = ? AND ngay_dat = ?";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, idPhongMay);
		hql.setParameter(1, idTietHoc);
		hql.setParameter(2, date);
		return hql.list();
	}

	private boolean checkStatus(String status, String statusFromRow){
		if (statusFromRow.equals("")){
			return false;
		}
		if (status==null){
			return true;
		} 
		if(Integer.parseInt(status)<Integer.parseInt(statusFromRow)){
			return true;
		} return false;
	}
	public List<LichDangKyStatus> getLichDangKyStatusByDate(Date date) {
		String queryString = "SELECT th.id, th.mo_ta, ldk.status, ldk.id_phong "
				+ "FROM tiet_hoc th "
				+ "LEFT JOIN lich_dang_ky ldk "
				+ "ON th.id = ldk.id_tiet_hoc "
				+ "AND ldk.ngay_dat = ?";
		SQLQuery hql = getSessionFactory().getCurrentSession().createSQLQuery(queryString);
		hql.setParameter(0, date);
		List<Object[]> rows = hql.list();
		List<LichDangKyStatus> lichDangKyStatusList = new ArrayList<LichDangKyStatus>();
		for (Object[] row: rows){		
			int indexRow = Integer.parseInt(row[0]+"")-1;
			LichDangKyStatus lichDangKyStatus;
			if (indexRow<lichDangKyStatusList.size()){
				lichDangKyStatus = lichDangKyStatusList.get(indexRow);
			} else {
				lichDangKyStatus = new LichDangKyStatus();
			}
			lichDangKyStatus.setIdTietHoc(row[0]+"");
			lichDangKyStatus.setMoTaTietHoc(row[1]+"");
			if ("1".equals(row[3]+"") 
					&& checkStatus(lichDangKyStatus.getStatus1(), row[2]+"")){
				lichDangKyStatus.setStatus1(row[2]+"");
			} else if ("2".equals(row[3]+"")
					&& checkStatus(lichDangKyStatus.getStatus2(), row[2]+"")){
				lichDangKyStatus.setStatus2(row[2]+"");
			} else if ("3".equals(row[3]+"")
					&& checkStatus(lichDangKyStatus.getStatus3(), row[2]+"")){
				lichDangKyStatus.setStatus3(row[2]+"");
			} else if ("4".equals(row[3]+"")
					&& checkStatus(lichDangKyStatus.getStatus4(), row[2]+"")){
				lichDangKyStatus.setStatus4(row[2]+"");
			} else if ("5".equals(row[3]+"")
					&& checkStatus(lichDangKyStatus.getStatus5(), row[2]+"")){
				lichDangKyStatus.setStatus5(row[2]+"");
			}			
			if (indexRow<lichDangKyStatusList.size()){
				lichDangKyStatusList.set(indexRow, lichDangKyStatus);
			} else{
				lichDangKyStatusList.add(indexRow, lichDangKyStatus);
			}
			
		}
		return lichDangKyStatusList;
	}

	public List<LichDangKy> hienThiDangKyTrungTheoNgay(Date date) {
		String queryString = "SELECT x.* FROM lich_dang_ky x "
				+ "INNER JOIN (SELECT * FROM lich_dang_ky "
								+ "WHERE ngay_dat = ? "
								+ "GROUP BY id_phong,id_tiet_hoc "
								+ "HAVING count(id_phong)>1 "
								+ "AND count(id_tiet_hoc)>1) y "
				+ "ON x.id_phong = y.id_phong "
				+ "AND x.id_tiet_hoc = y.id_tiet_hoc "
				+ " WHERE x.ngay_dat = ?";
		SQLQuery hql = getSessionFactory().getCurrentSession().createSQLQuery(queryString);
		hql.setParameter(0, date);
		hql.setParameter(1, date);
		List<Object[]> rows = hql.list();
		List<LichDangKy> lichDangKyList = new ArrayList<LichDangKy>();
		for (Object[] row:rows){
			LichDangKy lichDangKy = new LichDangKy();
			lichDangKy.setId(row[0]+"");
			lichDangKy.setIdPhong(row[1]+"");
			lichDangKy.setTenPhongMay(row[2]+"");
			lichDangKy.setIdTietHoc(row[3]+"");
			lichDangKy.setMoTaTietHoc(row[4]+"");
			lichDangKy.setTieuDe(row[5]+"");
			lichDangKy.setMoTa(row[6]+"");
			lichDangKy.setStatus(Integer.parseInt(row[7]+""));
			lichDangKy.setIdGiaoVien(row[8]+"");
			lichDangKy.setTenGiaoVien(row[9]+"");
			lichDangKy.setNgayDat((Date)row[10]);
			lichDangKy.setYeuCau(row[11]+"");
			lichDangKy.setEmailLop(row[12]+"");
			lichDangKyList.add(lichDangKy);
		}
		return lichDangKyList;
	}
	
	public List<LichDangKy> hienThiDangKyTrungTheoTuan(Date fromDate,Date toDate) {
		String queryString = "SELECT x.* FROM lich_dang_ky x "
				+ "INNER JOIN (SELECT * FROM lich_dang_ky "
								+ "WHERE ngay_dat >= ? "
								+ "AND ngay_Dat <= ?"
								+ "GROUP BY id_phong,id_tiet_hoc,ngay_dat "
								+ "HAVING count(id_phong)>1 "
								+ "AND count(id_tiet_hoc)>1 "
								+ "AND count(ngay_dat)>1) y "
				+ "ON x.id_phong = y.id_phong "
				+ "AND x.id_tiet_hoc = y.id_tiet_hoc "
				+ "AND x.ngay_dat = y.ngay_dat "
				+ "ORDER BY x.ngay_dat DESC";
		SQLQuery hql = getSessionFactory().getCurrentSession().createSQLQuery(queryString);
		hql.setParameter(0, fromDate);
		hql.setParameter(1, toDate);
		List<Object[]> rows = hql.list();
		List<LichDangKy> lichDangKyList = new ArrayList<LichDangKy>();
		for (Object[] row:rows){
			LichDangKy lichDangKy = new LichDangKy();
			lichDangKy.setId(row[0]+"");
			lichDangKy.setIdPhong(row[1]+"");
			lichDangKy.setTenPhongMay(row[2]+"");
			lichDangKy.setIdTietHoc(row[3]+"");
			lichDangKy.setMoTaTietHoc(row[4]+"");
			lichDangKy.setTieuDe(row[5]+"");
			lichDangKy.setMoTa(row[6]+"");
			lichDangKy.setStatus(Integer.parseInt(row[7]+""));
			lichDangKy.setIdGiaoVien(row[8]+"");
			lichDangKy.setTenGiaoVien(row[9]+"");
			lichDangKy.setNgayDat((Date)row[10]);
			lichDangKy.setYeuCau(row[11]+"");
			lichDangKy.setEmailLop(row[12]+"");
			lichDangKyList.add(lichDangKy);
		}
		return lichDangKyList;
	}

	public List<LichDangKy> getLichDangKyByDateAndUser(Date date, String userId, String status) {
		String queryString = "FROM LichDangKy WHERE ngay_dat = ? ";
		if (!userId.equals("")){
			queryString+="AND id_giao_vien = ?";
		}
		if (status.equals("-1")){
			queryString+="AND status=-1";
		}
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, date);
		if (!userId.equals("")){
			hql.setParameter(1, userId);
		}
		return hql.list();
	}

	public List<LichDangKy> getLichDangKyBy2DateAndChuaHuy(Date fromDate,Date toDate) {
		String queryString = "FROM LichDangKy WHERE ngay_dat >= ? AND ngay_dat <= ? AND status<>-1 ORDER BY ngay_dat DESC, id_phong, id_tiet_hoc, tieu_de";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, fromDate);
		hql.setParameter(1, toDate);
		return hql.list();
	}
	public List<LichDangKy> getLichDangKyByDateAndChuaHuy(Date date) {
		String queryString = "FROM LichDangKy WHERE ngay_dat = ? AND status<>-1 ORDER BY id_phong, id_tiet_hoc, tieu_de";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, date);
		return hql.list();
	}

	public List<LichDangKy> getLichDangKyBy2DateAndUser(Date fromDate,
			Date toDate, String userId,String status) {
		String queryString = "FROM LichDangKy WHERE ngay_dat >= ? AND ngay_dat <= ? ";
		if (!userId.equals("")){
			queryString+="AND id_giao_vien = ? ";
		}
		if (status.equals("-1")){
			queryString+="AND status=-1";
		}
		queryString+="ORDER BY ngay_dat DESC";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, fromDate);
		hql.setParameter(1, toDate);
		if (!userId.equals("")){
			hql.setParameter(2, userId);
		}
		return hql.list();
	}

	public List<LichDangKy> getLichDangKyTheoMonHocAndUser(String strMonHoc, String userId,String status) {
		String queryString = "FROM LichDangKy WHERE tieu_de LIKE ?";
		if (!userId.equals("")){
			queryString+="AND id_giao_vien = ? ";
		}
		if (status.equals("-1")){
			queryString+="AND status=-1";
		}
		queryString+="ORDER BY ngay_dat DESC";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		hql.setParameter(0, "%"+strMonHoc+"%");
		if (!userId.equals("")){
			hql.setParameter(1, userId);
		}		
		return hql.list();
	}

	public List<LichDangKy> thongKe(ThongKe thongKe) {
		String queryString = "FROM LichDangKy WHERE ";
		boolean check = false;//check dieu kien search
		if (!thongKe.getTenGiaoVien().equals("")){
			queryString+="ten_giao_vien LIKE ? AND ";
			check = true;
		}
		if (!thongKe.getTenMonHoc().equals("")){
			queryString+="tieu_de LIKE ? AND ";
			check = true;
		}
		if (thongKe.getFromDate()!=null){
			queryString+="ngay_dat>=? AND ";
			check = true;
		}
		if (thongKe.getToDate()!=null){
			queryString+="ngay_dat<=? AND ";
			check = true;
		}
		if (thongKe.getPhongMay()!=-1){
			queryString+="id_phong = ? AND ";
			check = true;
		}
		if (thongKe.getTietHoc()!=-1){
			queryString+="id_tiet_hoc = ? AND ";
			check = true;
		}
		if (thongKe.getStatus()!=-10){
			queryString+="status = ? ";
			if (thongKe.getStatus()==0){
				queryString+=" OR status = '-2'";
			}
			queryString+=" AND";
			check = true;
		}
		int lastIndex = 0;
		if (check){
			lastIndex = queryString.lastIndexOf("AND");			
		} else {
			lastIndex = queryString.lastIndexOf("WHERE");
		}
		queryString = queryString.substring(0, lastIndex);
		queryString+=" ORDER BY ngay_dat DESC,id_phong, id_tiet_hoc, tieu_de";
		Query hql = getSessionFactory().getCurrentSession().createQuery(queryString);
		int i=0;
		if (!thongKe.getTenGiaoVien().equals("")){
			hql.setParameter(i++, "%"+thongKe.getTenGiaoVien()+"%");
		}
		if (!thongKe.getTenMonHoc().equals("")){
			hql.setParameter(i++, "%"+thongKe.getTenMonHoc()+"%");
		}
		if (thongKe.getFromDate()!=null){
			hql.setParameter(i++, thongKe.getFromDate());
		}
		if (thongKe.getToDate()!=null){
			hql.setParameter(i++, thongKe.getToDate());
		}
		if (thongKe.getPhongMay()!=-1){
			hql.setParameter(i++, thongKe.getPhongMay());
		}
		if (thongKe.getTietHoc()!=-1){
			hql.setParameter(i++, thongKe.getTietHoc());
		}
		if (thongKe.getStatus()!=-10){
			hql.setParameter(i++, thongKe.getStatus());
		}
		return hql.list();
	}

}
